set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[a_adm_stats_sel]	
@PageSize int,
@PageSetSize int,
@sField nvarchar(50),
@sValue nvarchar(100),
@SortBy nvarchar(50),
@SortOrder nvarchar(4),
@pgNum int
AS
Set NoCount On
SET DATEFIRST 7


DECLARE @strSQL nvarchar(3000), @strCriteria nvarchar(1000), @strSort nvarchar(100), @strFilterDate nvarchar(1000), @strFilterStatus nvarchar(100),
	@FirstRec int, @LastRec int, @TotalRecords int, @TotalPages int, @TotalPageSet int, @CurrentSet int,
	@SetLBound int, @SetUBound int, @DateSign nvarchar(50)

declare @atb nvarchar(200), @ssort nvarchar(100), @sstatus nvarchar(100)
SET @sValue = REPLACE(@sValue,'''','''''')


--Get Search Results

	--Sort String
	IF  LEN(@SortBy) = 0
	    Set @strSort = ' Order by RefName ' --Use default if no sort
	ELSE
	    SET @strSort = ' ORDER BY ' + @SortBy+' '+@SortOrder

	--Search criteria
	IF LEN(@sField) > 0 AND Len(@sValue) > 0 
	begin
	    SET @strCriteria = 'WHERE ('+@sField+' LIKE N''%'+@sValue+'%'') '
	end
	ELSE
	    SET @strCriteria = '' 

--Get Search Results end


CREATE TABLE #TempList
(
	[ID] int IDENTITY(1,1),
	RefID int,
	RefName nvarchar(100),
	URL varchar(500),
	Today int,
	Yesterday int,
	Thismonth int,
	TotalHit int
)

	--Create a temporary table to implement pagesize	
	SET @strSQL = 'INSERT INTO #TempList (RefID, RefName, URL, Today, Yesterday, Thismonth, TotalHit) 
					select RefID, RefName, URL, 
					(	select count(*) from a_ReferDetail rd
						where rd.RefID = rs.RefID
						--today
						and datevisit = convert(varchar(50), getdate(), 101)
					) as Today, 
					(	select count(*) from a_ReferDetail rd
						where rd.RefID = rs.RefID
						--yesterdady
						and datevisit = convert(varchar(50), getdate() - 1, 101)
					) as Yesterday, 
					(	select count(*) from a_ReferDetail rd
						where rd.RefID = rs.RefID
						--this month
						and DateVisit >= convert(varchar(50), [dbo].[ufn_GetFirstDayOfMonth](getdate()) , 101) 	 and DateVisit < convert(varchar(50), getdate() +  1  , 101)
					) as Thismonth, 
					NumHits as TotalHit
					from a_ReferSources rs ' +  @strCriteria + @strSort


EXEC(@strSQL)


		 
	-- Find out the first and last record we want
	SELECT @FirstRec = (@pgNum - 1) * @PageSize
	SELECT @LastRec = (@pgNum * @PageSize + 1)
	
	SELECT @TotalRecords = COUNT(*) FROM #TempList
	
	--Calculate how many pages in this recordset
	If @TotalRecords % @PageSize > 0
	   Select @TotalPages = @TotalRecords/@PageSize + 1
	Else
	   Select @TotalPages = @TotalRecords/@PageSize
	
	--Calculate how many pageset in this recordset 
	If @TotalPages % @PageSetSize > 0	
	   Select @TotalPageSet = @TotalPages/@PageSetSize + 1
	Else
	   Select @TotalPageSet = @TotalPages/@PageSetSize
	
	--Calculate CurrentSet Number
	If @pgNum % @PageSetSize > 0
	   Select @CurrentSet = @pgNum / @PageSetSize + 1
	Else
	   Select @CurrentSet = @pgNum / @PageSetSize
	
	--Calculate Current Set Lower & Uppper Bounds
	Select @SetLBound = (@CurrentSet-1)*@PageSetSize + 1
	Select @SetUBound = @CurrentSet*@PageSetSize


	--OUTPUT paged values
	IF EXISTS(SELECT 'True' FROM #TempList WHERE ID > @FirstRec AND ID < @LastRec)
	Begin
		SELECT 	@TotalRecords AS TotalRecords, @TotalPages AS TotalPages, @TotalPageSet AS TotalPageSet, 
			@CurrentSet AS CurPgSet, @SetLBound AS SetLBound, @SetUBound AS SetUBound

	    SELECT RefID, RefName, URL, Today, Yesterday, Thismonth, TotalHit
	    FROM #TempList T 
		WHERE T.ID > @FirstRec AND T.ID < @LastRec

	End
 	ELSE
	    SELECT 0 as [no result]		




--Drop Temp table
DROP TABLE #TempList

THEEND:










